package com.googlecode.iiddefecttracker.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.object.MappingSqlQuery;

import com.googlecode.iiddefecttracker.domain.Defect;
import com.googlecode.iiddefecttracker.domain.DefectBeanImpl;
import com.googlecode.iiddefecttracker.domain.EntityNotFound;
import com.googlecode.iiddefecttracker.domain.Project;
import com.googlecode.iiddefecttracker.domain.ProjectBeanImpl;

/**
 * Base implementation of {@link ProjectDao} that uses Spring JDBC services.
 */
public class DefectDaoSpringJDBC extends SimpleJdbcDaoSupport implements
        DefectDao {
    
    private class DefectsAllQuery extends MappingSqlQuery {
        
        protected DefectsAllQuery(final DataSource ds) {
            super(
                    ds,
                    "SELECT id, project_id, summary, stepstorecreate, result, expectedresult, workaround, p.id, p.name, p.version, p.description FROM defects, projects p WHERE (p.id = project_id) ORDER BY id");
            compile();
        }
        
        @SuppressWarnings("unchecked")
        @Override
        public List<Defect> execute() throws DataAccessException {
            return super.execute();
        }
        
        @Override
        protected Defect mapRow(final ResultSet rs, final int rowNum)
                throws SQLException {
            final Long id = Long.valueOf(rs.getLong("id"));
            final Long projectId = Long.valueOf(rs.getLong("project_id"));
            final String summary = rs.getString("summary");
            final String stepsToRecreate = rs.getString("stepstorecreate");
            final String result = rs.getString("result");
            final String expectedResult = rs.getString("expectedresult");
            final String workaround = rs.getString("workaround");
            
            final DefectBeanImpl defect = new DefectBeanImpl();
            defect.setId(id);
            defect.setSummary(summary);
            defect.setStepsToRecreate(stepsToRecreate);
            defect.setResult(result);
            defect.setExpectedResult(expectedResult);
            defect.setWorkaround(workaround);
            
            final String name = rs.getString("name");
            final String version = rs.getString("version");
            final String description = rs.getString("description");
            
            final Project project = new ProjectBeanImpl();
            project.setId(projectId);
            project.setName(name);
            project.setVersion(version);
            project.setDescription(description);
            
            defect.setProject(project);
            
            return defect;
        }
    }
    
    private DefectsAllQuery defectsAllQuery;
    
    public List<Defect> findAllDefects() {
        return defectsAllQuery.execute();
    }
    
    public Defect findDefectById(final Long defectId) throws EntityNotFound {
        
        final String sql = "SELECT id, project_id, summary, stepstorecreate, result, expectedresult, workaround, p.id, p.name, p.version, p.description FROM defects, projects p WHERE (p.id = project_id) AND id = ? ORDER BY id";
        
        final ParameterizedRowMapper<Defect> mapper = new ParameterizedRowMapper<Defect>() {
            
            public Defect mapRow(final ResultSet rs, final int rowNum)
                    throws SQLException {
                
                final Long id = Long.valueOf(rs.getLong("id"));
                final Long projectId = Long.valueOf(rs.getLong("project_id"));
                final String summary = rs.getString("summary");
                final String stepsToRecreate = rs.getString("stepstorecreate");
                final String result = rs.getString("result");
                final String expectedResult = rs.getString("expectedresult");
                final String workaround = rs.getString("workaround");
                
                final DefectBeanImpl defect = new DefectBeanImpl();
                defect.setId(id);
                defect.setSummary(summary);
                defect.setStepsToRecreate(stepsToRecreate);
                defect.setResult(result);
                defect.setExpectedResult(expectedResult);
                defect.setWorkaround(workaround);
                
                final String name = rs.getString("name");
                final String version = rs.getString("version");
                final String description = rs.getString("description");
                
                final Project project = new ProjectBeanImpl();
                project.setId(projectId);
                project.setName(name);
                project.setVersion(version);
                project.setDescription(description);
                
                defect.setProject(project);
                
                return defect;
            }
        };
        
        try {
            return getSimpleJdbcTemplate().queryForObject(sql, mapper,
                    new Object[] { defectId });
        } catch (final EmptyResultDataAccessException e) {
            throw new EntityNotFound("A Defect with id: " + defectId
                    + " does not exist.", e);
        }
        
    }
    
    public void saveDefect(final Defect defect) {
        final Object[] queryParams = new Object[] {
                defect.getProject().getId(), defect.getSummary(),
                defect.getStepsToRecreate(), defect.getResult(),
                defect.getExpectedResult(), defect.getWorkaround() };
        
        getSimpleJdbcTemplate()
                .update(
                        "insert into defects (project_id, summary, stepstorecreate, result, expectedresult, workaround) values (?,?,?,?,?,?)",
                        queryParams);
    }
    
    @Override
    protected void initDao() throws Exception {
        super.initDao();
        defectsAllQuery = new DefectsAllQuery(getDataSource());
    }
}